﻿Imports System.Data.SqlClient

Public Class RechercheLP

    Private user As String
    Private userSite As String
    Private cn As CnData
    Private da As SqlDataAdapter
    Private ds As DataSet
    Private cmd As SqlCommand
    Private dtr As SqlDataReader

    Public Sub New(ByVal user As String, ByRef cn As CnData, ByVal userSite As String)
        ' Cet appel est requis par le concepteur.
        InitializeComponent()
        ' Ajoutez une initialisation quelconque après l'appel InitializeComponent().
        Me.user = user
        Me.cn = cn
        Me.userSite = userSite
    End Sub


    Private Sub RechercheLP_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cn.Open()
        da = New SqlDataAdapter("SELECT '00' as NumSite,'' as NomDoc FROM Sites UNION SELECT NumSite,NomDoc FROM Sites WHERE Actif = 1", cn.getCn)
        ds = New DataSet
        da.Fill(ds, "SitesD")
        With siteE
            .DataSource = ds.Tables("SitesD")
            .DisplayMember = "NomDoc"
            .ValueMember = "NumSite"
            .SelectedValue = "00"
        End With
        da = New SqlDataAdapter("SELECT '00' as NumSite,'' as NomDoc FROM Sites UNION SELECT NumSite,NomDoc FROM Sites WHERE Actif = 1", cn.getCn)
        da.Fill(ds, "SitesD1")
        With siteD
            .DataSource = ds.Tables("SitesD1")
            .DisplayMember = "NomDoc"
            .ValueMember = "NumSite"
            .SelectedValue = "00"
        End With
        da = New SqlDataAdapter("SELECT '00' as NumFlux, '' as NomFlux FROM Flux UNION SELECT NumFlux,NomFlux FROM Flux WHERE Actif = 1 ORDER BY NomFlux", cn.getCn)
        da.Fill(ds, "Flux")
        With Typef
            .DataSource = ds.Tables("Flux")
            .DisplayMember = "NomFlux"
            .ValueMember = "NumFlux"
            .SelectedIndex = 0
        End With
        Date1.Value = Date.Today
        Date1.Checked = False

        'da = New SqlDataAdapter("select fin.* from (select lpc.lp as lp, (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
        '            & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
        '            & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
        '            & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
        '            & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
        '            & " lpc.statut as statut_reception, lpc.anomalie as anomalie_réception, " _
        '            & " lpc.chargement as chargement, " _
        '            & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
        '            & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
        '            & " ch.dateexp as date_expedition, ch.daterec as date_reception, ch.statut as statut_chargement " _
        '            & " from lp_chargement lpc " _
        '            & " inner join chargements ch on ch.Chargement = lpc.Chargement " _
        '            & " inner join LP lp on lpc.LP = lp.LP " _
        '            & " inner join zr zr on zr.IdZR = lp.IdZR " _
        '            & " union ( " _
        '            & " select lpe.lp as lp,  " _
        '            & " (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
        '            & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
        '            & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
        '            & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
        '            & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
        '            & " null, lpe.anomalie as anomalie_reception, " _
        '            & " lpe.chargement as chargement, " _
        '            & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
        '            & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
        '            & " ch.dateexp as date_expedition, ch.daterec as date_reception, ch.statut as statut_chargement " _
        '            & " from lp_excedent lpe " _
        '            & " inner join chargements ch on ch.Chargement = lpe.Chargement " _
        '            & " inner join LP lp on lpe.LP = lp.LP " _
        '            & " inner join zr zr on zr.IdZR = lp.IdZR) " _
        '            & " ) fin WHERE fin.statut_lp = 'Non reçu' order by date_expedition desc", cn.getCn)
        da = New SqlDataAdapter("select TOP 10000 fin.* from ((select lpc.lp as lp, (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
                    & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
                    & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
                    & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
                    & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
                    & " lpc.statut as statut_reception, lpc.anomalie as anomalie_réception, " _
                    & " lpc.chargement as chargement, " _
                    & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
                    & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
                    & " ch.dateexp as date_expedition, ch.daterec as date_reception, lp.DateImp  as date_impression,  ch.statut as statut_chargement " _
                    & " from lp_chargement lpc " _
                    & " inner join chargements ch on ch.Chargement = lpc.Chargement " _
                    & " inner join LP lp on lpc.LP = lp.LP " _
                    & " left join zr zr on zr.IdZR = lp.IdZR)" _
                    & " union ( " _
                    & " select lpe.lp as lp,  " _
                    & " (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
                    & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
                    & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
                    & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
                    & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
                    & " null, lpe.anomalie as anomalie_reception, " _
                    & " lpe.chargement as chargement, " _
                    & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
                    & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
                    & " ch.dateexp as date_expedition, ch.daterec as date_reception,  lp.DateImp  as date_impression,  ch.statut as statut_chargement" _
                    & " from lp_excedent lpe " _
                    & " inner join chargements ch on ch.Chargement = lpe.Chargement " _
                    & " inner join LP lp on lpe.LP = lp.LP " _
                    & " left join zr zr on zr.IdZR = lp.IdZR) " _
                    & " ) fin WHERE statut_lp = 'Non reçu' and date_expedition > '01/01/2099' order by date_expedition desc", cn.getCn)
        'da.Fill(ds, "ListeC")
        If da.Fill(ds, "ListeC") >= 10000 Then MsgBox("Seuls les 10 000 derniers enregistrements sont affichés")
        Liste.DataSource = ds.Tables("ListeC")
        cn.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Cursor = Cursors.WaitCursor
        Dim tmp As String
        tmp = "WHERE 1=1"
        If Date1.Checked Then tmp = tmp & " AND fin.date_reception = '" & Date1.Value & "'"
        If Not String.IsNullOrEmpty(lp.Text) Then tmp = tmp & " AND fin.lp = '" & lp.Text & "'"
        If siteE.SelectedValue <> "00" Then tmp = tmp & " AND fin.ch_expediteur = '" & siteE.Text & "'"
        If siteD.SelectedValue <> "00" Then tmp = tmp & " AND fin.ch_destinataire = '" & siteD.Text & "'"
        If Not String.IsNullOrEmpty(statut.Text) Then tmp = tmp & " AND fin.statut_lp = '" & statut.Text & "'"
        If Not String.IsNullOrEmpty(rayon.Text) Then tmp = tmp & " AND fin.rayon = '" & rayon.Text & "'"
        If Typef.SelectedValue <> "00" Then tmp = tmp & " AND fin.type_flux = '" & Typef.Text & "'"
        'MsgBox(tmp)
        da = New SqlDataAdapter("select top 10000 fin.* from (select lpc.lp as lp, (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
                    & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
                    & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
                    & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
                    & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
                    & " lpc.statut as statut_reception, lpc.anomalie as anomalie_réception, " _
                    & " lpc.chargement as chargement, " _
                    & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
                    & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
                    & " ch.dateexp as date_expedition, ch.daterec as date_reception,  lp.DateImp  as date_impression, ch.statut as statut_chargement " _
                    & " from lp_chargement lpc " _
                    & " inner join chargements ch on ch.Chargement = lpc.Chargement " _
                    & " inner join LP lp on lpc.LP = lp.LP " _
                    & " left join zr zr on zr.IdZR = lp.IdZR " _
                    & " union ( " _
                    & " select lpe.lp as lp,  " _
                    & " (select NomFlux from Flux where NumFlux = lp.Flux) as type_flux, " _
                    & " zr.rayon as rayon, zr.info1_doc as lib_rayon, zr.info2_eti as marques, " _
                    & " (select nomdoc from sites where numsite = lp.expediteur) as de, " _
                    & " (select nomDoc from Sites where NumSite = lp.Destinataire) as vers, " _
                    & " lp.typeagres as type_agres, lp.nombrecolis as nombre_colis, lp.statut as statut_lp, " _
                    & " null, lpe.anomalie as anomalie_reception, " _
                    & " lpe.chargement as chargement, " _
                    & " (select nomDoc from Sites where NumSite = ch.Expediteur) as ch_expediteur,  " _
                    & " (select nomDoc from Sites where NumSite = ch.Destinataire) as ch_destinataire,  " _
                    & " ch.dateexp as date_expedition, ch.daterec as date_reception,  lp.DateImp  as date_impression ,  ch.statut as statut_chargement" _
                    & " from lp_excedent lpe " _
                    & " inner join chargements ch on ch.Chargement = lpe.Chargement " _
                    & " inner join LP lp on lpe.LP = lp.LP " _
                    & " left join zr zr on zr.IdZR = lp.IdZR) " _
                    & " ) fin " & tmp & "  order by date_expedition desc", cn.getCn)
        'da.Fill(ds, "ListeC")
        ds.Tables("ListeC").Clear()
        If da.Fill(ds, "ListeC") >= 10000 Then
            'Me.Cursor = Cursors.Default
            MsgBox("Seuls les 10 000 derniers enregistrements sont affichés")
        End If
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub recherche_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles recherche.KeyPress
        If e.KeyChar = Chr(34) Or e.KeyChar = "'" Or e.KeyChar = "*" Then
            e.KeyChar = ""
            Exit Sub
        End If
    End Sub

    Private Sub recherche_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles recherche.TextChanged
        Dim retrdvview As New DataView
        On Error GoTo err
        With retrdvview
            .Table = ds.Tables("ListeC")
            .RowFilter = "[lp]+' '+[type_flux]+' '+[date_impression] like '%" & recherche.Text & "%'"
        End With
        Liste.DataSource = retrdvview
        Exit Sub
Err:
        MsgBox("Saisie incorrecte")
        recherche.Text = ""
    End Sub

    Private Sub Fermer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Fermer.Click
        Me.Close()
    End Sub

    Private Sub lp_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles lp.KeyPress
        If e.KeyChar = Chr(34) Or e.KeyChar = "'" Or e.KeyChar = "*" Then
            e.KeyChar = ""
            Exit Sub
        End If
    End Sub

    Private Sub rayon_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles rayon.KeyPress
        If e.KeyChar = Chr(34) Or e.KeyChar = "'" Or e.KeyChar = "*" Then
            e.KeyChar = ""
            Exit Sub
        End If
    End Sub

    Private Sub ExtraireButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExtraireButton.Click
        Dim c, ligne As Integer
        Dim xl As New Microsoft.Office.Interop.Excel.Application

        xl.Visible = False
        xl.Workbooks.Add()
        xl.Worksheets(1).Range("A1:S1").Font.bold = True
        xl.Worksheets(1).Columns("A").NumberFormat = "@"
        xl.Worksheets(1).Columns("M").NumberFormat = "@"

        Try
            For ligne = 0 To Liste.Rows.Count - 1
                For c = 0 To Liste.Columns.Count - 1
                    xl.Cells(1, c + 1) = Liste.Columns(c).Name
                    xl.Cells(ligne + 2, c + 1) = Liste.Rows(ligne).Cells(c).Value
                    xl.Range("A1:S1").EntireColumn.Columns.AutoFit()
                Next (c)
            Next ligne
            xl.Visible = True
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        xl = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub
End Class